﻿CREATE TABLE [dbo].[Persoane] (
    [IdPersoana]            INT              IDENTITY (1, 1) NOT NULL,
    [NumePrenume]           VARCHAR (150)    NOT NULL,
    [Nume_Anterior]         VARCHAR (50)     NULL,
    [Cnp]                   DECIMAL (13)     NOT NULL,
    [Sex]                   SMALLINT         CONSTRAINT [DF_PERSOANE_Sex] DEFAULT ((0)) NOT NULL,
    [DataNasterii]          DATETIME         NOT NULL,
    [IDLocNastere]          INT              NOT NULL,
    [TipAI]                 SMALLINT         DEFAULT ((0)) NOT NULL,
    [SeriaAI]               VARCHAR (3)      NOT NULL,
    [NumarAI]               VARCHAR (15)     NOT NULL,
    [DataElibAI]            DATETIME         NOT NULL,
    [EmitentAI]             VARCHAR (100)    NOT NULL,
    [NumeTata]              VARCHAR (50)     NULL,
    [NumeMama]              VARCHAR (50)     NULL,
    [StradaAdr]             VARCHAR (100)    NOT NULL,
    [IDLocalitateAdr]       INT              NOT NULL,
    [NrAdr]                 VARCHAR (10)     NOT NULL,
    [BlocAdr]               VARCHAR (10)     NOT NULL,
    [EtajAdr]               VARCHAR (10)     NULL,
    [ScaraAdr]              VARCHAR (10)     NULL,
    [ApartamentAdr]         VARCHAR (10)     NOT NULL,
    [GradInvaliditate]      SMALLINT         NULL,
    [TelefonFix]            VARCHAR (20)     NULL,
    [TelefonMobil]          VARCHAR (20)     NOT NULL,
    [Observatii]            VARCHAR (100)    NULL,
    [NumarCM]               VARCHAR (10)     CONSTRAINT [DF_Persoane_NumarCM] DEFAULT ('') NOT NULL,
    [SerieCM]               VARCHAR (10)     CONSTRAINT [DF_Persoane_SerieCM] DEFAULT ('') NOT NULL,
    [CodUnitate]            SMALLINT         CONSTRAINT [DF_Persoane_CodUnitate] DEFAULT ((0)) NOT NULL,
    [NrFileCM]              SMALLINT         CONSTRAINT [DF_Persoane_NrFileCM] DEFAULT ((0)) NOT NULL,
    [NrCutieCM]             VARCHAR (10)     CONSTRAINT [DF_Persoane_NrCutieCM] DEFAULT ('') NOT NULL,
    [Marca]                 VARCHAR (20)     CONSTRAINT [DF_Persoane_Marca] DEFAULT ('FARA') NOT NULL,
    [TelefonInterior]       VARCHAR (20)     NULL,
    [IdCasaAsigSanatate]    SMALLINT         CONSTRAINT [DF_Persoane_IdCasaAsigSanatate] DEFAULT ((0)) NOT NULL,
    [IdTipStudii]           INT              CONSTRAINT [DF_Persoane_IdTipStudii] DEFAULT ((0)) NOT NULL,
    [DataVechimeMunca]      DATETIME         CONSTRAINT [DF_Persoane_DataVechimeMunca] DEFAULT (getdate()) NOT NULL,
    [UserID]                UNIQUEIDENTIFIER NULL,
    [PozCutieCM]            SMALLINT         CONSTRAINT [DF_Persoane_PozCutieCM] DEFAULT ((0)) NOT NULL,
    [PlatesteCAS]           BIT              CONSTRAINT [DF_Persoane_PlatesteCAS] DEFAULT ((1)) NOT NULL,
    [PlatesteCASS]          BIT              CONSTRAINT [DF_Persoane_PlatesteCASS] DEFAULT ((1)) NOT NULL,
    [PlatesteSomaj]         BIT              CONSTRAINT [DF_Persoane_PlatesteSomaj] DEFAULT ((1)) NOT NULL,
    [PlatesteImpozit]       BIT              CONSTRAINT [DF_Persoane_PlatesteImpozit] DEFAULT ((1)) NOT NULL,
    [AreDeducere]           BIT              CONSTRAINT [DF_Persoane_AreDeducere] DEFAULT ((1)) NOT NULL,
    [DataPlecarii]          DATETIME         NULL,
    [MotivIncetareContract] VARCHAR (250)    NULL,
    CONSTRAINT [PK_PEOPLE_1__10] PRIMARY KEY CLUSTERED ([IdPersoana] ASC) WITH (FILLFACTOR = 90),
    CONSTRAINT [FK_PERSOANE_LOC_NASTERE] FOREIGN KEY ([IDLocNastere]) REFERENCES [dbo].[Localitati] ([IDLocalitate]),
    CONSTRAINT [FK_PERSOANE_LOCALITATI] FOREIGN KEY ([IDLocalitateAdr]) REFERENCES [dbo].[Localitati] ([IDLocalitate]) ON DELETE CASCADE ON UPDATE CASCADE,
    CONSTRAINT [FK_Persoane_Localitati_Nastere] FOREIGN KEY ([IDLocNastere]) REFERENCES [dbo].[Localitati] ([IDLocalitate]),
    CONSTRAINT [FK_Persoane_Unitati] FOREIGN KEY ([CodUnitate]) REFERENCES [dbo].[Unitati] ([CodUnitate])
);


GO


CREATE TRIGGER [dbo].[audit_Persoane] 
   ON  [dbo].[Persoane]
   AFTER INSERT,DELETE,UPDATE
AS 
BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT ON;
    INSERT INTO [admin_Audit]
           ([UserId]
           ,[TableName]
           ,[Operation]
           ,[CampID]
           ,[Data])
     SELECT coalesce([UserID],newid()),'Persoane', 'A', IDPersoana, getdate() FROM inserted i
		WHERE i.IDPersoana not in (SELECT IDPersoana from deleted)	 

    INSERT INTO [admin_Audit]
           ([UserId]
           ,[TableName]
           ,[Operation]
           ,[CampID]
           ,[Data])
     SELECT coalesce(i.UserID,newid()),'Persoane', 'M', i.IDPersoana, getdate() FROM inserted i
		JOIN deleted d on i.IDPersoana=d.IDPersoana

    INSERT INTO [admin_Audit]
           ([UserId]
           ,[TableName]
           ,[Operation]
           ,[CampID]
           ,[Data])
     SELECT coalesce([UserID],newid()),'Persoane', 'D', IDPersoana, getdate() FROM deleted d
		WHERE d.IDPersoana not in (SELECT IDPersoana from inserted)	 


END